package process;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CreateDB
{
//Tạo cơ sở dữ liệu

    private Connection connect;
    PreparedStatement stmt;
    private String createSchema = "CREATE SCHEMA IF NOT EXISTS `db_project_hotel_manager_team_ttq` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;";
    private String createTblCustomer = "CREATE  TABLE IF NOT EXISTS `db_project_hotel_manager_team_ttq`.`customer` ("
            + "`ID` INT NOT NULL AUTO_INCREMENT ,"
            + "`Full Name` VARCHAR(45) NOT NULL ,"
            + "`Birthday` INT NOT NULL ,"
            + "`Identify Card` VARCHAR(45) NOT NULL ,"
            + "`Gender` VARCHAR(10) NOT NULL ,"
            + "`Phone` BIGINT NOT NULL ,"
            + "`Addrdess` VARCHAR(45) NOT NULL ,"
            + "PRIMARY KEY (`ID`) )"
            + " ENGINE = InnoDB"
            + " AUTO_INCREMENT = 50"
            + " DEFAULT CHARACTER SET = utf8;";
    private String createTblCustomerhistory = "CREATE  TABLE IF NOT EXISTS `db_project_hotel_manager_team_ttq`.`customerhistory` ("
            + "`ID` INT NOT NULL AUTO_INCREMENT ,"
            + "`Full Name` VARCHAR(45) NOT NULL ,"
            + "`Birthday` INT NOT NULL ,"
            + "`Gender` VARCHAR(10) NOT NULL ,"
            + "`Identify Card` VARCHAR(45) NOT NULL ,"
            + "`Addrdess` VARCHAR(45) NOT NULL ,"
            + "`Phone` BIGINT NOT NULL ,"
            + "`Room` VARCHAR(45) NOT NULL ,"
            + "`Type` VARCHAR(45) NOT NULL ,"
            + "`Date Coming` DATE NOT NULL ,"
            + "`Date Leaving` DATE NOT NULL ,"
            + "`SumCost` VARCHAR(45) NOT NULL ,"
            + "PRIMARY KEY (`ID`) )"
            + " ENGINE = InnoDB"
            + " AUTO_INCREMENT = 47"
            + " DEFAULT CHARACTER SET = utf8;";
    private String createTblTyperoom = "CREATE  TABLE IF NOT EXISTS `db_project_hotel_manager_team_ttq`.`typeroom` ("
            + "`Type` VARCHAR(45) NOT NULL ,"
            + "`Price` VARCHAR(45) NOT NULL ,"
            + "PRIMARY KEY (`Type`) )"
            + " ENGINE = InnoDB"
            + " DEFAULT CHARACTER SET = utf8;";
    private String createTblRoom = "CREATE  TABLE IF NOT EXISTS `db_project_hotel_manager_team_ttq`.`room` ("
            + "`Name` INT NOT NULL ,"
            + "`State` VARCHAR(45) NOT NULL ,"
            + "`Type` VARCHAR(45) NOT NULL ,"
            + "PRIMARY KEY (`Name`) ,"
            + "INDEX `fk_room_typeroom1_idx` (`Type` ASC) ,"
            + "CONSTRAINT `fk_room_typeroom1`"
            + "  FOREIGN KEY (`Type` )"
            + " REFERENCES `db_project_hotel_manager_team_ttq`.`typeroom` (`Type` )"
            + " ON DELETE NO ACTION"
            + " ON UPDATE NO ACTION)"
            + " ENGINE = InnoDB"
            + " DEFAULT CHARACTER SET = utf8;";
    private String createTblReservation = "CREATE  TABLE IF NOT EXISTS `db_project_hotel_manager_team_ttq`.`reservation` ("
            + "`ID` INT NOT NULL AUTO_INCREMENT ,"
            + "`Date Coming` DATE NOT NULL ,"
            + "`Room` INT NOT NULL ,"
            + "`CustomerID` INT NOT NULL ,"
            + "PRIMARY KEY (`ID`) ,"
            + "INDEX `fk_reservation_room1_idx` (`Room` ASC) ,"
            + "INDEX `fk_reservation_customer1_idx` (`CustomerID` ASC) ,"
            + " CONSTRAINT `fk_reservation_room1`"
            + " FOREIGN KEY (`Room` )"
            + " REFERENCES `db_project_hotel_manager_team_ttq`.`room` (`Name` )"
            + " ON DELETE NO ACTION"
            + " ON UPDATE NO ACTION,"
            + " CONSTRAINT `fk_reservation_customer1`"
            + " FOREIGN KEY (`CustomerID` )"
            + " REFERENCES `db_project_hotel_manager_team_ttq`.`customer` (`ID` )"
            + " ON DELETE NO ACTION"
            + " ON UPDATE NO ACTION)"
            + " ENGINE = InnoDB"
            + " AUTO_INCREMENT = 9"
            + " DEFAULT CHARACTER SET = utf8;";
    private String createTblAccount = "CREATE  TABLE IF NOT EXISTS `db_project_hotel_manager_team_ttq`.`account` ("
            + "`user` VARCHAR(45) NOT NULL ,"
            + "`pass` VARCHAR(45) NULL ,"
            + "`priority` VARCHAR(45) NOT NULL ,"
            + "PRIMARY KEY (`user`) )"
            + " ENGINE = InnoDB"
            + " DEFAULT CHARACTER SET = utf8;";
    private String addUser = "INSERT INTO `db_project_hotel_manager_team_ttq`.`account` (`user`, `pass`, `priority`) VALUES ('admin', 'admin', 'Admin');";

    public CreateDB() throws SQLException
    {
        connect = ConnectDB.getConnect();

        //Tạo Schema
        stmt = connect.prepareStatement(createSchema);
        stmt.execute();

        //Tạo các bảng
        stmt = connect.prepareStatement(createTblAccount);
        stmt.execute();
        stmt = connect.prepareStatement(createTblCustomer);
        stmt.execute();
        stmt = connect.prepareStatement(createTblCustomerhistory);
        stmt.execute();
        stmt = connect.prepareStatement(createTblTyperoom);
        stmt.execute();
        stmt = connect.prepareStatement(createTblRoom);
        stmt.execute();
        stmt = connect.prepareStatement(createTblReservation);
        stmt.execute();
        
        
        stmt = connect.prepareStatement(addUser);
        stmt.execute();
        stmt.close();
        connect.close();
        ConnectDB.closeConnect();
    }
}
